package test.demo;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

import static jxl.biff.FormatRecord.logger;

public class ExcelTimeInterpolation {
    public static void main(String[] args) throws Exception {
        String inputFilePath = "C:\\Users\\25468\\Desktop\\水库原始数据\\4.2降雨站点数据对比\\柴河\\1974-2013年逐时段降雨量表.xlsx";
        String outputFilePath = "C:\\Users\\25468\\Desktop\\水库原始数据\\4.2降雨站点数据对比\\柴河\\3shuchu1974-2013年逐时段降雨量表.xlsx";

        FileInputStream fis = new FileInputStream(inputFilePath);
        Workbook workbook = new XSSFWorkbook(fis);
        Workbook outputWorkbook = new XSSFWorkbook();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Sheet outputSheet = outputWorkbook.createSheet(sheet.getSheetName());

            List<Row> rows = new ArrayList<>();
            for (Row row : sheet) {
                rows.add(row);
            }

            // 处理时间列
            List<Date> originalTimes = new ArrayList<>();
            Map<Date, Row> rowMap = new HashMap<>();

            for (Row row : rows) {
                Cell timeCell = row.getCell(0);
                // POI 5.x: 直接比较枚举类型
                if (timeCell != null && timeCell.getCellType() == CellType.NUMERIC) {
                    Date time = timeCell.getDateCellValue();
                    originalTimes.add(time);
                    rowMap.put(time, row);
                }
            }

            Collections.sort(originalTimes);

            // 生成新的时间序列（间隔1小时）
            List<Date> newTimes = new ArrayList<>();
            Calendar calendar = Calendar.getInstance();
            if (!originalTimes.isEmpty()) {
                calendar.setTime(originalTimes.get(0));
                while (!calendar.getTime().after(originalTimes.get(originalTimes.size() - 1))) {
                    newTimes.add(calendar.getTime());
                    calendar.add(Calendar.HOUR_OF_DAY, 1);
                }
            }

            // 创建新表头
            Row headerRow = outputSheet.createRow(0);
            for (int j = 0; j < rows.get(0).getPhysicalNumberOfCells(); j++) {
                headerRow.createCell(j).setCellValue(rows.get(0).getCell(j).toString());
            }

            // 填充数据
            int rowIndex = 1;
            for (Date newTime : newTimes) {
                Row newRow = outputSheet.createRow(rowIndex++);
                Cell timeCell = newRow.createCell(0);
                timeCell.setCellValue(newTime);

                if (rowMap.containsKey(newTime)) {
                    Row oldRow = rowMap.get(newTime);
                    for (int j = 1; j < oldRow.getPhysicalNumberOfCells(); j++) {
                        Cell oldCell = oldRow.getCell(j);
                        if (oldCell != null) {
                            Cell newCell = newRow.createCell(j);
                            // POI 5.x: 直接比较枚举类型
                            if (oldCell.getCellType() == CellType.NUMERIC) {
                                newCell.setCellValue(oldCell.getNumericCellValue());
                            } else {
                                newCell.setCellValue(oldCell.toString());
                            }
                        }
                    }
                }
            }
        }

        fis.close();
        FileOutputStream fos = new FileOutputStream(outputFilePath);
        outputWorkbook.write(fos);
        fos.close();
        outputWorkbook.close();
        workbook.close();

        System.out.println("处理完成，数据已写入: " + outputFilePath);
    }
}